<?php
    namespace Home\Controller;
    use Think\Controller;
    class KeshikpikaoheController extends Controller {
    
    public function _initialize()
    {
        header("Access-Control-Allow-Origin: *");  

        header("Access-Control-Allow-Credentials: true"); 
      
        header("Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS"); //允许的请求类型
        
        header("Access-Control-Allow-Headers: Origin, X-Requested-With, Content-Type, Accept"); 

    }

    public function get_list()
    {
        $page = I("get.page");
        $limit = I("get.limit");



        $sql = "SELECT DEPT_CODE,
         DEPT_NAME,
         ROUND (SUM (CASE WHEN BSC_CLASS LIKE '01%' THEN T_VALUE ELSE 0 END),2)
            AS GUIDE_F_VALUE_01,
         ROUND (SUM (CASE WHEN BSC_CLASS LIKE '02%' THEN T_VALUE ELSE 0 END),2)
            AS GUIDE_F_VALUE_02,
         ROUND (SUM (CASE WHEN BSC_CLASS LIKE '03%' THEN T_VALUE ELSE 0 END),2)
            AS GUIDE_F_VALUE_03,
         ROUND (SUM (CASE WHEN BSC_CLASS LIKE '04%' THEN T_VALUE ELSE 0 END),2)
            AS GUIDE_F_VALUE_04,
         ROUND (SUM (T_VALUE),2) AS ALL_VALUE,
         ROUND (SUM (DEST_VALUE),2) AS DESTVALUE
    FROM (  SELECT R.DEPT_CODE,
                   D.DEPT_NAME,
                   R.BSC_CLASS,
                   SUM (R.GUIDE_F_VALUE) T_VALUE,
                   SUM (R.GUIDE_VALUE) AS DEST_VALUE
              FROM COMM.SYS_DEPT_DICT D, HOSPITALSYS.DEPT_ASSESS_TEMP R
             WHERE R.DEPT_CODE = D.DEPT_CODE
            
          GROUP BY R.DEPT_CODE, D.DEPT_NAME, R.BSC_CLASS)
GROUP BY DEPT_CODE, DEPT_NAME
ORDER BY DEPT_CODE";

        $data = M()->query($sql);

        $json["data"] = $data;
        $json["code"] = 0;
        $json["count"] = 0;
        $json["msg"] = "";
        
        $this->ajaxReturn($json,"json");
    }

    public function get_one()
    {
        $uuid = I("post.uuid");

        $info = M("hospitalsys.dept_assess_result")->where("uuid = '$uuid'")->find();
        $info["dept_date"] = date("Y-m-d H:i:s",$info["dept_date"]);
        $json["data"]   = $info;
        $json["code"]   = 0;
        $json["msg"]    = "";

        $this->ajaxReturn($json,"json");
    }

    public function save_one()
    {
        $data = I("post.");
        $uuid = $data["uuid"];
        unset($data["uuid"]);

        $result = M("hospitalsys.dept_assess_result")->where("uuid = '$uuid'")->save($data);
        if($result !== false)
        {
            $json["code"] = 0;
            $json["msg"] = "修改成功";
        }
        else
        {
            $json["code"] = -1;
            $json["msg"] = "修改失败";
        }
 
        $this->ajaxReturn($json,"json");
    }

    public function add_one()
    {
        $data = I("post.");
        $uuid = uuid();
        
        $data["dept_date"] = time();
        $result = M("hospitalsys.dept_assess_result")->add($data);
        if($result !== false)
        {
            $json["code"] = 0;
            $json["msg"] = "添加成功";
        }
        else
        {
            $json["code"] = -1;
            $json["msg"] = "添加失败";
        }
 
        $this->ajaxReturn($json,"json");
    }

    public function show()
    {
        $dept_code = I('get.dept_code');

        $sql = "SELECT G.GUIDE_CODE,
         S.BSC_CLASS_NAME,
         S.BSC_CLASS,
         G.GUIDE_NAME,
         ROUND (S.GUIDE_VALUE, 2) AS GUIDE_VALUE,
         S.GUIDE_CAUSE AS GUIDE_CAUSE,
         ROUND (S.GUIDE_FACT, 2) AS GUIDE_FACT,
         ROUND (S.GUIDE_I_VALUE, 2) AS GUIDE_I_VALUE,
         ROUND (S.GUIDE_D_VALUE, 2) AS GUIDE_D_VALUE,
         ROUND (S.GUIDE_F_VALUE, 2) AS GUIDE_F_VALUE,
         CASE WHEN S.GUIDE_F_VALUE >= 0 THEN '1' ELSE '0' END FLAG
    FROM (  SELECT T.*, J.BSC_CLASS_NAME
              FROM HOSPITALSYS.DEPT_ASSESS_TEMP T,
                   HOSPITALSYS.JXGL_GUIDE_BSC_CLASS_DICT J,
                   HOSPITALSYS.GUIDE_NAME_DICT L
             WHERE     SUBSTR (T.BSC_CLASS, 1, 2) = J.BSC_CLASS_CODE
                   AND T.GUIDE_CODE = L.GUIDE_CODE
                   AND T.DEPT_CODE = '$dept_code'
          ORDER BY T.DEPT_CODE) S,
         HOSPITALSYS.GUIDE_NAME_DICT G
   WHERE G.GUIDE_CODE = S.GUIDE_CODE
ORDER BY G.GUIDE_CODE";
        
        // echo $sql;
        // die();

        $data = M()->query($sql);

        $json["data"] = $data;
        $json["code"] = 0;
        $json["count"] = 0;
        $json["msg"] = "";
        
        $this->ajaxReturn($json,"json");
    }



    public function create_data()
    {
        
        $data = I('post.');
        $data['year'] = date("Y",strtotime($data['date']));
        $data['date'] = date("Ym",strtotime($data['date']));

        //建立数据库连接
        $user = "system";                //数据库用户名
        $password = "123456";            //密码
        $conn_str = "(DESCRIPTION=(ADDRESS=(PROTOCOL =TCP)(HOST=localhost)(PORT = 1521))(CONNECT_DATA =(SID=orcl)))";          //连接串(cstr : Connection_STRing)
        $remote = true;                  //是否远程连接
        if ($remote) 
        {
          $conn = OCILogon($user, $password, $conn_str);
        }
        else 
        {
          $conn = OCILogon($user, $password);
        }//设定绑定
        $id = 5;                        //准备用以绑定的php变量 id
        $name = "";                     //准备用以绑定的php变量 name/** 调用存储过程的sql语句(sql_sp : SQL_StoreProcedure)
        //  *  语法：
        //  *      BEGIN 存储过程名([[:]参数]); END;
        //  *  加上冒号表示该参数是一个位置
        // **/
        $sql_sp = "BEGIN hospitalsys.DEPT_ASSESS_COMPUTE(:date, :guide_code,:year); END;";
        //Parse
        $stmt = OCIParse($conn, $sql_sp);//执行绑定

        $username = "SYSTEM";
        OCIBindByName($stmt, ":date", $data['date'], 16);           //参数说明：绑定php变量$id到位置:id，并设定绑定长度16位
        OCIBindByName($stmt, ":guide_code", $username, 30);//Execute
        OCIBindByName($stmt, ":year", $data['year'], 30);
        OCIExecute($stmt);

        
        echo 1;

    }

    public function save_data()
    {
        $date = I('post.date');

        if(empty($date))
        {
            die();
        }

        $all_count = 0;
        $success_count = 0;
        $fail_count = 0;

        $assess_code = date('Ym',strtotime($date));
        $date = strtotime($date);

        M("hospitalsys.dept_assess_result")->where("assess_code = '$assess_code' and station_year = '$date'")->delete();

        $list = M("hospitalsys.dept_assess_temp")->select();
        for ($i=0; $i < count($list); $i++) 
        { 
            $info = $list[$i];
            unset($info['exdate']);
            unset($info['counting']);
            unset($info['duty_order']);
            unset($info['organ']);
            unset($info['numrow']);
            $info['assess_code'] = $assess_code;
            $info['station_year'] = $date;

            $result = M("hospitalsys.dept_assess_result")->add($info);
            if($result !== false)
            {
                $success_count++;
            }
            else
            {
                $fail_count++;
            }
            $all_count++;
        }

        $json["data"] = null;
        $json["code"] = 0;
        $json["count"] = 0;
        $json["msg"] = "共操作".$all_count."条数据，".$success_count."条成功，".$fail_count."条失败";
        
        $this->ajaxReturn($json,"json");

    }

    public function get_result_list()
    {
        $page = I("get.page");
        $limit = I("get.limit");
        $date = strtotime(I('get.date'));
		$admin_uuid = I('admin_uuid');
		$admin_dept_list_str = get_user_dept_list($admin_uuid);
		
        $sql = "SELECT DEPT_CODE,
         DEPT_NAME,
         ROUND (SUM (CASE WHEN BSC_CLASS LIKE '01%' THEN T_VALUE ELSE 0 END),2)
            AS GUIDE_F_VALUE_01,
         ROUND (SUM (CASE WHEN BSC_CLASS LIKE '02%' THEN T_VALUE ELSE 0 END),2)
            AS GUIDE_F_VALUE_02,
         ROUND (SUM (CASE WHEN BSC_CLASS LIKE '03%' THEN T_VALUE ELSE 0 END),2)
            AS GUIDE_F_VALUE_03,
         ROUND (SUM (CASE WHEN BSC_CLASS LIKE '04%' THEN T_VALUE ELSE 0 END),2)
            AS GUIDE_F_VALUE_04,
         ROUND (SUM (T_VALUE),2) AS ALL_VALUE,
         ROUND (SUM (DEST_VALUE),2) AS DESTVALUE
    FROM (  SELECT R.DEPT_CODE,
                   D.DEPT_NAME,
                   R.BSC_CLASS,
                   SUM (R.GUIDE_F_VALUE) T_VALUE,
                   SUM (R.GUIDE_VALUE) AS DEST_VALUE
              FROM COMM.SYS_DEPT_DICT D, HOSPITALSYS.DEPT_ASSESS_RESULT R
             WHERE R.DEPT_CODE = D.DEPT_CODE
             AND R.STATION_YEAR = '".$date."'
			 AND D.dept_code in ($admin_dept_list_str)
          GROUP BY R.DEPT_CODE, D.DEPT_NAME, R.BSC_CLASS)
GROUP BY DEPT_CODE, DEPT_NAME
ORDER BY DEPT_CODE";

        $data = M()->query($sql);

        $json["data"] = $data;
        $json["code"] = 0;
        $json["count"] = 0;
        $json["msg"] = "";
        
        $this->ajaxReturn($json,"json");
    }

    public function show_result()
    {
        $dept_code = I('get.dept_code');

        $sql = "SELECT G.GUIDE_CODE,
         S.BSC_CLASS_NAME,
         S.BSC_CLASS,
         G.GUIDE_NAME,
         ROUND (S.GUIDE_VALUE, 2) AS GUIDE_VALUE,
         S.GUIDE_CAUSE AS GUIDE_CAUSE,
         ROUND (S.GUIDE_FACT, 2) AS GUIDE_FACT,
         ROUND (S.GUIDE_I_VALUE, 2) AS GUIDE_I_VALUE,
         ROUND (S.GUIDE_D_VALUE, 2) AS GUIDE_D_VALUE,
         ROUND (S.GUIDE_F_VALUE, 2) AS GUIDE_F_VALUE,
         CASE WHEN S.GUIDE_F_VALUE >= 0 THEN '1' ELSE '0' END FLAG
    FROM (  SELECT T.*, J.BSC_CLASS_NAME
              FROM HOSPITALSYS.DEPT_ASSESS_RESULT T,
                   HOSPITALSYS.JXGL_GUIDE_BSC_CLASS_DICT J,
                   HOSPITALSYS.GUIDE_NAME_DICT L
             WHERE      SUBSTR (T.BSC_CLASS, 1, 2) = J.BSC_CLASS_CODE
                   AND T.GUIDE_CODE = L.GUIDE_CODE
                   AND T.DEPT_CODE = '$dept_code'
          ORDER BY T.DEPT_CODE) S,
         HOSPITALSYS.GUIDE_NAME_DICT G
   WHERE G.GUIDE_CODE = S.GUIDE_CODE
ORDER BY G.GUIDE_CODE";
        
        // echo $sql;
        // die();

        $data = M()->query($sql);

        $json["data"] = $data;
        $json["code"] = 0;
        $json["count"] = 0;
        $json["msg"] = "";
        
        $this->ajaxReturn($json,"json");
    }


}